#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
 -------------------------------------------------
    File Name:     mysql_handler.py
    Description:   MySQL 操作工具类
 -------------------------------------------------
"""

from __future__ import annotations

import pymysql
import logging

from dbutils.pooled_db import PooledDB  # 用于连接池
from pymysql.cursors import DictCursor
from typing import Any, List, Tuple

# from DBUtils.PooledDB import PooledDB  # 弃用

# Configure logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

# 模块公开接口
__all__ = ["AppMySQL", "mysql_handler"]


class MySQLDB:
    def __init__(
            self,
            host: str = "localhost",
            port: int = 3306,
            username: str = "root",
            password: str = "",
            database: str = "",
            charset: str = "utf8mb4",
            max_retries: int = 3,
            pool_size: int = 10,
    ) -> None:
        """
        初始化 MySQL 连接池。
        :param host: MySQL 主机地址
        :param port: MySQL 端口
        :param username: MySQL 用户名
        :param password: MySQL 密码
        :param database: 默认数据库
        :param charset: 字符集
        :param max_retries: 最大重试次数
        :param pool_size: 连接池大小
        """
        self.max_retries = max_retries
        try:
            self.pool = PooledDB(
                creator=pymysql,
                maxconnections=pool_size,
                mincached=1,
                blocking=True,
                ping=0,
                host=host,
                port=port,
                user=username,
                password=password,
                database=database,
                charset=charset,
                cursorclass=DictCursor,  # 返回字典形式结果
            )
            logger.info(f"Connected to MySQL at {host}:{port} (database={database})")
        except Exception as e:
            logger.error(f"Failed to initialize MySQL connection pool: {e}")
            raise

    def _get_connection(self):
        """
        获取数据库连接。
        """
        return self.pool.connection()

    def execute(self, sql: str, params: Tuple[Any, ...] = None) -> int:
        """
        执行单条 SQL 语句（如 INSERT/UPDATE/DELETE）。
        :param sql: SQL 语句
        :param params: 参数
        :return: 受影响的行数
        """
        connection = self._get_connection()
        try:
            with connection.cursor() as cursor:
                affected_rows = cursor.execute(sql, params)
                connection.commit()
                logger.info(f"Executed SQL: {sql}, Affected rows: {affected_rows}")
                return affected_rows
        except Exception as e:
            connection.rollback()
            logger.error(f"Error executing SQL: {sql}, Error: {e}")
            raise
        finally:
            connection.close()

    def fetch_one(self, sql: str, params: Tuple[Any, ...] = None) -> dict:
        """
        查询单条记录。
        :param sql: SQL 语句
        :param params: 参数
        :return: 查询结果（字典）
        """
        connection = self._get_connection()
        try:
            with connection.cursor() as cursor:
                cursor.execute(sql, params)
                result = cursor.fetchone()
                logger.info(f"Executed SQL: {sql}, Result: {result}")
                return result
        except Exception as e:
            logger.error(f"Error fetching one for SQL: {sql}, Error: {e}")
            raise
        finally:
            connection.close()

    def fetch_all(self, sql: str, params: Tuple[Any, ...] = None) -> List[dict]:
        """
        查询多条记录。
        :param sql: SQL 语句
        :param params: 参数
        :return: 查询结果（字典列表）
        """
        connection = self._get_connection()
        try:
            with connection.cursor() as cursor:
                cursor.execute(sql, params)
                results = cursor.fetchall()
                logger.info(f"Executed SQL: {sql}, Retrieved rows: {len(results)}")
                return results
        except Exception as e:
            logger.error(f"Error fetching all for SQL: {sql}, Error: {e}")
            raise
        finally:
            connection.close()

    def execute_many(self, sql: str, param_list: List[Tuple[Any, ...]]) -> int:
        """
        批量执行 SQL（如批量 INSERT）。
        :param sql: SQL 语句
        :param param_list: 参数列表
        :return: 受影响的行数
        """
        connection = self._get_connection()
        try:
            with connection.cursor() as cursor:
                affected_rows = cursor.executemany(sql, param_list)
                connection.commit()
                logger.info(f"Executed batch SQL: {sql}, Affected rows: {affected_rows}")
                return affected_rows
        except Exception as e:
            connection.rollback()
            logger.error(f"Error executing batch SQL: {sql}, Error: {e}")
            raise
        finally:
            connection.close()

    def execute_transaction(self, queries: List[Tuple[str, Tuple[Any, ...]]]) -> None:
        """
        执行事务。
        :param queries: SQL 语句和参数的列表
        """
        connection = self._get_connection()
        try:
            with connection.cursor() as cursor:
                for sql, params in queries:
                    cursor.execute(sql, params)
                connection.commit()
                logger.info(f"Transaction executed successfully")
        except Exception as e:
            connection.rollback()
            logger.error(f"Transaction failed: {e}")
            raise
        finally:
            connection.close()


class AppMySQL(MySQLDB):
    def __init__(self):
        from apitesting.config import settings
        super().__init__(
            host=settings.mysql.host,
            port=settings.mysql.port,
            username=settings.mysql.username,
            password=settings.mysql.password,
            database=settings.mysql.database
        )


mysql_handler = AppMySQL()

# 示例：如何在自动化框架中调用
if __name__ == "__main__":
    AppMySQL()

    # 模块初始化
    # db = MySQLDB(
    #     host="localhost",
    #     port=3306,
    #     username="root",
    #     password="password",
    #     database="test_db"
    # )

    # 示例操作
    # try:
    #     # 插入数据
    #     insert_sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
    #     db.execute(insert_sql, ("John Doe", "john@example.com"))
    #
    #     # 查询数据
    #     select_sql = "SELECT * FROM users WHERE email = %s"
    #     user = db.fetch_one(select_sql, ("john@example.com",))
    #     print(f"Fetched User: {user}")
    #
    #     # 批量插入
    #     batch_sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
    #     params = [("Jane Doe", "jane@example.com"), ("Alice", "alice@example.com")]
    #     db.execute_many(batch_sql, params)
    #
    #     # 查询所有数据
    #     all_users = db.fetch_all("SELECT * FROM users")
    #     print(f"All Users: {all_users}")
    #
    #     # 执行事务
    #     transaction_queries = [
    #         ("UPDATE users SET name = %s WHERE email = %s", ("John Smith", "john@example.com")),
    #         ("DELETE FROM users WHERE email = %s", ("alice@example.com",))
    #     ]
    #     db.execute_transaction(transaction_queries)
    #
    # except Exception as e:
    #     print(f"Error: {e}")
